package com.bijie.etl.control

import java.util.Properties

import org.apache.spark.sql.SparkSession

object bijieControl {
    def main(args: Array[String]): Unit = {

        val spark: SparkSession = SparkSession
          .builder()
          .master("local[*]")
          .appName("JDBCRead")
          .getOrCreate()

        val url = "jdbc:mysql://hadoop102:3306/excelToMysql"
        val table = "ent_position_require_info"
        val props: Properties = new Properties()
        props.setProperty("user", "root")
        props.setProperty("password", "123456")

        spark.read.jdbc(url,table,props).createOrReplaceTempView("entPositionRequit")

        spark.sql(
            """
              |select
              |      ent_name,
              |      industry_type,
              |       case when industry_type ="居民服务业" then "900001"
              |       when industry_type ="银行业" then "900002"
              |       when industry_type ="农林牧渔业" then "900003"
              |       when industry_type ="中国共产党机关" then "900004"
              |       when industry_type ="国际组织" then "900005"
              |       when industry_type ="住宿业" then "900006"
              |       when industry_type ="房屋与土木工程建筑业" then "900007"
              |       when industry_type ="电力、热力的生产和供应业" then "900008"
              |       when industry_type ="房地产业" then "900009"
              |       when industry_type ="农副食品加工业" then "900010"
              |       when industry_type ="研究与试验发展" then "900011"
              |       when industry_type ="教育" then "900012"
              |       when industry_type ="卫生" then "900013"
              |       when industry_type ="租赁业" then "900014"
              |       when industry_type ="批发业" then "900015"
              |       when industry_type ="新闻出版业" then "900016"
              |       when industry_type ="铁路运输业" then "900017"
              |       when industry_type ="煤炭开采与洗选业" then "900018"
              |       when industry_type ="水利管理业" then "900019"
              |       when industry_type ="电信和其他信息传输服务业" then "900020"
              |       when industry_type ="农业" then "900021"
              |       when industry_type ="林业" then "900022"
              |       when industry_type ="零售业" then "900023"
              |       when industry_type ="食品制造业" then "900024"
              |       when industry_type ="石油和天然气开采业" then "900025"
              |       when industry_type ="采矿业" then "900026"
              |       when industry_type ="社会保障业" then "900027"
              |       when industry_type ="其他服务业" then "900028"
              |       when industry_type ="环境管理业" then "900029"
              |       when industry_type ="专业技术服务业" then "900030"
              |       when industry_type ="商务服务业" then "900031"
              |       when industry_type ="餐饮业" then "900032"
              |       when industry_type ="道路运输业" then "900033"
              |       when industry_type ="建筑安装业" then "900034"
              |       when industry_type ="计算机服务业" then "900035"
              |       when industry_type ="证券业" then "900036"
              |       when industry_type ="燃气生产和供应业" then "900037"
              |       when industry_type ="国家机构" then "900038"
              |       when industry_type ="广播、电视、电影和音像业" then "900039"
              |       when industry_type ="公共设施管理业" then "900040"
              |       when industry_type ="水的生产和供应业" then "900041"
              |       when industry_type ="制造业" then "900042"
              |       when industry_type ="科技交流和推广服务业" then "900043"
              |       when industry_type ="黑色金属矿采选业" then "900044"
              |       when industry_type ="建筑装饰业" then "900045"
              |       when industry_type ="畜牧业" then "900046"
              |       when industry_type ="城市公共交通业" then "900047"
              |       when industry_type ="人民政协和民主党派" then "900048"
              |       when industry_type ="社会福利业" then "900049"
              |       when industry_type ="文化艺术业" then "900050"
              |       when industry_type ="软件业" then "900051"
              |       when industry_type ="保险业" then "900052"
              |       when industry_type ="饮料制造业" then "900053"
              |       when industry_type ="地质勘查业" then "900054"
              |       when industry_type ="渔业" then "900055"
              |       when industry_type ="有色金属矿采选业" then "900056"
              |       when industry_type ="烟草制品业" then "900057"
              |       when industry_type ="电力、煤气及水生产和供应" then "900058"
              |       when industry_type ="其他金融活动" then "900059"
              |       when industry_type ="体育" then "900060"
              |       when industry_type ="群众团体、社会团体和宗教组织" then "900061"
              |       when industry_type ="其他建筑业" then "900062"
              |       when industry_type ="水上运输业" then "900063"
              |       when industry_type ="基层群众自治组织" then "900064"
              |       when industry_type ="娱乐业" then "900065"
              |       when industry_type ="建筑业" then "900066"
              |       when industry_type ="航空运输业" then "900067"
              |       when industry_type ="农、林、牧、渔服务业" then "900068"
              |       when industry_type ="非金属矿采选业" then "900069"
              |       when industry_type ="纺织业" then "900070"
              |       when industry_type ="其他采矿业" then "900071"
              |       when industry_type ="纺织服装、鞋、帽制造业" then "900072"
              |       when industry_type ="管道运输业" then "900073"
              |       when industry_type ="交通运输、仓储和邮政业" then "900074"
              |       when industry_type ="信息传输、计算机服务和软件业" then "900075"
              |       when industry_type ="皮革、毛皮、羽毛（绒及其制品业）" then "900076"
              |       when industry_type ="装卸搬运和其他运输服务业" then "900077"
              |       when industry_type ="木材加工及木、竹、藤、棕、草制品业" then "900078"
              |       when industry_type ="仓储业" then "900079"
              |       when industry_type ="批发和零售业" then "900080"
              |       when industry_type ="邮政业" then "900081"
              |       when industry_type ="住宿和餐饮业" then "900082"
              |       when industry_type ="家具制造业" then "900083"
              |       when industry_type ="金融业" then "900084"
              |       when industry_type ="造纸及纸制品业" then "900085"
              |       when industry_type ="印刷业和记录媒介的复制" then "900086"
              |       when industry_type ="租赁和商务服务业" then "900087"
              |       when industry_type ="文教体育用品制造业" then "900088"
              |       when industry_type ="石油加工、炼焦及核燃料加工业" then "900089"
              |       when industry_type ="科学研究、技术服务和地质勘查业" then "900090"
              |       when industry_type ="水利、环境和公共设施管理业" then "900091"
              |       when industry_type ="化学原料及化学制品制造业" then "900092"
              |       when industry_type ="居民服务和其他服务业" then "900093"
              |       when industry_type ="医药制造业" then "900094"
              |       when industry_type ="化学纤维制造业" then "900095"
              |       when industry_type ="卫生、社会保障和社会福利业" then "900096"
              |       when industry_type ="橡胶制品业" then "900097"
              |       when industry_type ="文化、体育和娱乐业" then "900098"
              |       when industry_type ="塑料制品业" then "900099"
              |       when industry_type ="非金属矿物制品业" then "900100"
              |       when industry_type ="公共管理和社会组织" then "900101"
              |       when industry_type ="黑色金属冶炼及压延加工业" then "900102"
              |       when industry_type ="有色金属冶炼及压延加工业" then "900103"
              |       when industry_type ="金属制品业" then "900104"
              |       when industry_type ="通用设备制造业" then "900105"
              |       when industry_type ="专用设备制造业" then "900106"
              |       when industry_type ="交通运输设备制造业" then "900107"
              |       when industry_type ="电气机械及器材制造业" then "900108"
              |       when industry_type ="通信设备、计算机及其他电子设备制造业" then "900109"
              |       when industry_type ="仪器仪表及文化、办公用机械制造业" then "900110"
              |       when industry_type ="工艺品及其他制造业" then "900111"
              |       when industry_type ="废弃资源和废旧材料回收加工业" then "900112"
              |      else null end as industry_code,
              |      business_nature,
              |      case when business_nature ="国有企业" then  "1000"
              |      when business_nature = "私营企业" then "1001"
              |      when business_nature = "私营企业" then "1001"
              |      when business_nature = "股份制企业" then "1002"
              |      when business_nature = "集体企业" then "1002"
              |      else "其他" end as business_nature_code,
              |      scale,
              |      address_code,
              |      registered_address,
              |      regexp_extract(registered_address,'(.*?省)',1) as province,
              |      regexp_extract(registered_address,'(.*?省)(.*?市)',2) as city,
              |      regexp_extract(registered_address,'(.*?省)(.*?市)(.*?区)',3) as area1,
              |      regexp_extract(registered_address,'(.*?省)(.*?市)(.*?县)',3) as area2,
              |      post_code,
              |      legal_person,
              |      legal_person_phone,
              |      contact_name,
              |      case when length(contact_phone)=8 then substring(contact_phone,1,7)
              |      when length(contact_phone)=12 then substring(contact_phone,1,11) end as contact_phone1,
              |      email,
              |      organization_code_certificate,
              |      business_license,
              |      uniform_social_credit_code,
              |      position,
              |      treatment,
              |      position_require,
              |      work_address,
              |      experience,
              |      education,
              |      case when education like '%小学%' then '小学'
              |      when education like '%初中%' then '初中'
              |      when education like '%中转%' then '中转'
              |      when education like '%高中%' then '高中'
              |      when education like '%大专%' then '大专'
              |      when education like '%本科%' then '本科'
              |      when education like '%硕士%' then '大专'
              |      else education end as education1,
              |      job_nature,
              |      case when job_nature ='全职' then '0'
              |      when job_nature ='兼职' then '1'
              |      when job_nature ='临时' then '2'
              |      when job_nature ='实习' then '3'
              |      end as job_nature_code,
              |      salary_begin,
              |      salary_end
              |from entPositionRequit
              |
              |
            """.stripMargin).createOrReplaceTempView("t1")

        //todo 匹配地区code
        spark.sql(
            """
              |
              |
              |
            """.stripMargin).createOrReplaceTempView("t2")

        spark.close()
    }
}
